<?php

class MySqlDAO {
    
    private function initDB() {
        include (dirname(__FILE__)."/../../conf/config.inc.php");
        $databaseURL = $dbConfig['databaseURL'];
        $databaseUserName = $dbConfig['databaseUserName'];
        $databasePassword = $dbConfig['databasePassword'];
        $databaseName = $dbConfig['databaseName'];
        $connection = mysql_connect($databaseURL, $databaseUserName, $databasePassword)
                or die("Errore connessione al database server");
        $db = mysql_select_db($databaseName)
                or die("Errore selezione database");
        return $connection;
    }

    private function closeDB($connection) {
        mysql_close($connection) or die("Errore chiusura connessione");
    }

    public function authenticateUser($username) {        
        $connection = $this->initDB();
        $query = "SELECT idu, password FROM users WHERE username='$username'";
        $result = mysql_query($query);
        $user = mysql_fetch_object($result);
        $this->closeDB($connection);
        return $user;
    }

    public function getLinkTitle($idl) {
        $connection = $this->initDB();
        $query = "SELECT title FROM links WHERE idl='$idl'";
        $result = mysql_query($query);
        $row = mysql_fetch_array($result, MYSQL_NUM);
        $this->closeDB($connection);
        return $row[0];
    }

    public function getFile($idf) {
        $connection = $this->initDB();
        $query = "SELECT title, filename, type, size, content FROM files WHERE idf='$idf'";
        $result = mysql_query($query);
        $this->closeDB($connection);
        return $result;
    }

    public function getFileTitle($idf) {
        $connection = $this->initDB();
        $query = "SELECT title FROM files WHERE idf='$idf'";
        $result = mysql_query($query);
        $row = mysql_fetch_array($result, MYSQL_NUM);
        $this->closeDB($connection);
        return $row[0];
    }

    public function getFileName($idf) {
        $connection = $this->initDB();
        $query = "SELECT filename FROM files WHERE idf='$idf'";
        $result = mysql_query($query);
        $row = mysql_fetch_array($result, MYSQL_NUM);
        $this->closeDB($connection);
        return $row[0];
    }

    public function getFilePath($idf) {
        $connection = $this->initDB();
        $query = "SELECT path FROM files WHERE idf='$idf'";
        $result = mysql_query($query);
        $row = mysql_fetch_array($result, MYSQL_NUM);
        $this->closeDB($connection);
        return $row[0];
    }

    public function getFiles($idc) {
        $connection = $this->initDB();
        $query = "SELECT idf, title, filename, type, size, date_ins FROM files WHERE idc='$idc'";
        $result = mysql_query($query);
        $this->closeDB($connection);
        return $result;
    }

    public function getCategory($idc) {
        $connection = $this->initDB();
        $query = "SELECT id, name, path, parent_dir FROM categories WHERE idc='$idc'";
        $result = mysql_query($query);
        $this->closeDB($connection);
        return $result;
    }

    public function getCategoryName($idc) {
        $connection = $this->initDB();
        $query = "SELECT name FROM categories WHERE idc='$idc'";
        $result = mysql_query($query);
        $row = mysql_fetch_array($result, MYSQL_NUM);
        $this->closeDB($connection);
        return $row[0];
    }

    public function getCategoryPath($idc) {
        $connection = $this->initDB();
        $query = "SELECT path FROM categories WHERE idc='$idc'";
        $result = mysql_query($query);
        $row = mysql_fetch_array($result, MYSQL_NUM);
        $this->closeDB($connection);
        return $row[0];
    }
    
    public function getParentDir($idc) {
        $connection = $this->initDB();
        $query = "SELECT parent_dir FROM categories WHERE idc='$idc'";
        $result = mysql_query($query);
        $row = mysql_fetch_array($result, MYSQL_NUM);
        $this->closeDB($connection);
        return $row[0];
    }

    public function getCategories($idc) {
        $connection = $this->initDB();
        $query = "SELECT idc, name FROM categories WHERE parent_dir='$idc' ORDER BY name ASC";
        $result = mysql_query($query);
        $this->closeDB($connection);
        return $result;
    }

    public function getLink($idl) {
        $connection = $this->initDB();
        $query = "SELECT title, url FROM links WHERE idl='$idl'";
        $result = mysql_query($query);
        $link = mysql_fetch_object($result);
        $this->closeDB($connection);
        return $link;
    }

    public function getLinks($idc) {
        $connection = $this->initDB();
        $query = "SELECT idl, title, url FROM links WHERE idc='$idc'";
        $result = mysql_query($query);
        $this->closeDB($connection);
        return $result;
    }

    public function createFile($title, $path, $fileName, $fileSize, $fileType, $idc) {
        $connection = $this->initDB();
        $query = "INSERT INTO files (title, path, filename, size, type, date_ins, idc) VALUES ('" . mysql_real_escape_string($title) . "', '" . mysql_real_escape_string($path) . "', '$fileName', '$fileSize', '$fileType', now(), $idc)";
        $result = mysql_query($query);
        $this->closeDB($connection);
        return $result;
    }

    public function createCategory($name, $path, $parent_dir) {
        $connection = $this->initDB();
        $query = "INSERT INTO categories (name, path, parent_dir) VALUES ('" . mysql_real_escape_string($name) . "', '" . mysql_real_escape_string($path) . "', " . $parent_dir . ");";
        $result = mysql_query($query);
        $this->closeDB($connection);
        return $result;
    }

    public function createLink($title, $url, $idc) {
        $connection = $this->initDB();
        $query = "INSERT INTO links (title, url, date_ins, idc) VALUES ('" . mysql_real_escape_string($title) . "', '$url', now(), '$idc')";
        $result = mysql_query($query);
        $this->closeDB($connection);
        //return $result;
    }

    public function deleteCategory($idc) {
        $connection = $this->initDB();
        $query = "DELETE FROM categories WHERE idc=$idc";
        $result = mysql_query($query);

        $query = "DELETE FROM categories WHERE parent_dir=$idc";
        $result = mysql_query($query);

        $this->closeDB($connection);
        return $result;
    }

    public function deleteFile($idf) {
        $connection = $this->initDB();
        $query = "DELETE FROM files WHERE idf=$idf";
        $result = mysql_query($query);
        $this->closeDB($connection);
        return $result;
    }

    public function deleteLink($idl) {
        $connection = $this->initDB();
        $query = "DELETE FROM links WHERE idl=$idl";
        $result = mysql_query($query);
        $this->closeDB($connection);
        return $result;
    }

    public function modifyCategory($idc, $name, $path, $parent_dir) {
        $connection = $this->initDB();
        $query = "UPDATE categories SET name='" . mysql_real_escape_string($name) . "', path='" . mysql_real_escape_string($path) . "', parent_dir=$parent_dir WHERE idc=$idc";
        $result = mysql_query($query);
        $this->closeDB($connection);
        //return $result;
    }

    public function modifyLink($idl, $title, $url, $idc) {
        $connection = $this->initDB();
        $query = "UPDATE links SET title='" . mysql_real_escape_string($title) . "', url='$url' WHERE idl=$idl";
        $result = mysql_query($query);
        $this->closeDB($connection);
        //return $result;
    }

    public function modifyFile($idf, $title, $path, $fileName, $fileSize, $fileType, $idc) {
        $connection = $this->initDB();
        if ($path == NULL)
            $query = "UPDATE files SET title='" . mysql_real_escape_string($title) . "' WHERE idf=$idf";
        else
            $query = "UPDATE files SET title='" . mysql_real_escape_string($title) . "', path='$path', filename='$fileName', size='$fileSize', type='$fileType' WHERE idf=$idf";
        $result = mysql_query($query);
        $this->closeDB($connection);
        //return $result;
    }

    public function checkCategoryName($parent_dir, $name) {
        $connection = $this->initDB();
        $query = "SELECT idc FROM categories WHERE parent_dir=$parent_dir AND name='$name'";
        $result = mysql_query($query);
        $row = mysql_fetch_array($result, MYSQL_NUM);
        $this->closeDB($connection);
        return $row[0];
    }

    public function checkFileExist($idc, $filename) {
        $connection = $this->initDB();
        $query = "SELECT title FROM files WHERE idc=$idc AND filename='$filename'";
        $result = mysql_query($query);
        $row = mysql_fetch_array($result, MYSQL_NUM);
        $this->closeDB($connection);
        return $row[0];
    }

    public function checkFileTitle($idc, $title) {
        $connection = $this->initDB();
        $query = "SELECT idf FROM files WHERE idc=$idc AND title='" . mysql_real_escape_string($title) . "'";
        $result = mysql_query($query);
        $row = mysql_fetch_array($result, MYSQL_NUM);
        $this->closeDB($connection);
        return $row[0];
    }

    public function checkLinkTitle($idc, $title) {
        $connection = $this->initDB();
        $query = "SELECT idl FROM links WHERE idc=$idc AND title='" . mysql_real_escape_string($title) . "'";
        $result = mysql_query($query);
        $row = mysql_fetch_array($result, MYSQL_NUM);
        $this->closeDB($connection);
        return $row[0];
    }

    public function inizializeRoot($name, $path) {
        $connection = $this->initDB();
        $query = "INSERT INTO categories (idc, name, path) VALUES (1, '$name', '$path')";
        $result = mysql_query($query);
        $this->closeDB($connection);
        return $result;
    }

}

?>
